package com.supermap.dataservice.cloud.stat;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.logging.log4j.util.Strings;

import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description
 * @date 2019年11月28 15:58
 * @email 1301584033@qq.com
 */
@Mapper
public interface StatMapper {
    class  StatMapperSQL{
        public String getFarmingListSQL(Map<String,Object> map){
            String areaCode=(String)map.get("areaCode");
            Integer userId=(Integer)map.get("userId");
            Integer farmId=(Integer)map.get("farmId");
            Integer page=(Integer)map.get("page");
            Integer size=(Integer)map.get("size");
            page=(page-1)*size;
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("select t5.username as userName,t5.showName as userName," +
                    "concat(\"http://www.cqpisa.cn/ChinaAPP/\",t5.HeadImage) as icon," +
                    "t2.areaname as areaName,t4.name as cropName,t3.name as farmingName,t1.description as content," +
                    "REPLACE(REPLACE(REPLACE(REPLACE(t1.img_url,'[',''),']',''),'\"',''),'img','http://www.cqpisa.cn/ChinaAPP/img') as imgs," +
                    "left(t1.recordtime,19) as cdate,t1.address,t1.weaphen,t1.temp," +
                    "concat(if(t1.weaphen is null,'',t1.weaphen),',',if(t1.temp is null,'',t1.temp),','," +
                    "if(t4.name is null,'',t4.name),',', if(t3.name is null,'',t3.name)) lab " +
                    "from t_farming t1\n" +
                    "inner join t_area t2\n" +
                    "on t1.areacode=t2.areacode\n" +
                    "inner join t_farmingtype t3\n" +
                    "on t1.`type`=t3.code\n" +
                    "inner join t_crop t4\n" +
                    "on t1.croptype=t4.code\n" +
                    "inner join t_user t5\n" +
                    "on t1.userid=t5.id\n" +
                    "where 1=1 ");
            if(Strings.isNotBlank(areaCode) && !"86".equals(areaCode)){
                stringBuilder.append(" and t2.areacode like concat('"+areaCode+"','%')");
            }
            if(farmId!=null){
                stringBuilder.append(" and t1.farmlandid ="+farmId+" ");
            }
            if(userId!=null){
                stringBuilder.append(" and t1.userid ="+userId+" ");
            }
            stringBuilder.append("   order by cdate desc limit "+page+","+size+" ");
            return  stringBuilder.toString();
        }
    }
    @SelectProvider(type = StatMapperSQL.class,method = "getFarmingListSQL")
    List<Map<String,Object>>  getFarmingList(@Param("areaCode") String areaCode, @Param("userId")Integer userId, @Param("farmId") Integer farmId, @Param("page") Integer page, @Param("size")Integer size);

    @Select("select s.name,s.icon,count(s.code)  num from  sys_menu_new s \n" +
            "inner join sys_area_menu_new ss on s.code =ss.menucode \n" +
            "left join t_user_click_menu t on t.menuCode = s.code and  if(#{areaCode} =86,1=1,t.areacode like concat(#{areaCode},'%')) \n" +
            "where length(s.code)>=6 and ss.areacode = left(#{areaCode},2) \n" +
            "group by s.code \n" +
            "order by num desc \n" +
            "limit #{limit}")
    List<Map<String,Object>> getMenuClickCountList(@Param("areaCode") String areaCode,@Param("limit") Integer limit);

    @Select("select sum(num) num from \n" +
            "(select count(s.id)  num  from  sys_menu_new s \n" +
            "inner join sys_area_menu_new ss on s.code =ss.menucode \n" +
            "left join t_user_click_menu t on t.menuCode = s.code and  if(#{areaCode} =86,1=1,t.areacode like concat(#{areaCode},'%')) \n" +
            "where length(s.code)>=6 and ss.areacode = left(#{areaCode},2) \n" +
            "group by s.id) t")
     Integer  getMenuClickCountListSumNum(@Param("areaCode") String areaCode);

    //决策产品
    @Select("select t1.id,t1.fit_areaid,count(t2.id) num from  cqpamiss.t_b_productgroup t1\n" +
            "inner join cqpamiss.t_b_pms_product t2 on t1.id = t2.product_group\n" +
            "where t2.product_state = 6 and t2.make_date between #{btime} and #{etime}\n" +
            "group by t1.id\n" +
            "union\n" +
            "select t1.id,t1.fit_areaid,count(t2.id) num from  china_pamiss.t_b_productgroup t1\n" +
            "inner join china_pamiss.t_b_pms_product t2 on t1.id = t2.product_group\n" +
            "where t2.product_state = 6 and t2.make_date between #{btime} and #{etime}\n" +
            "group by t1.id")
    List<Map<String,Object>> getSmsProject(@Param("btime")String btime,@Param("etime")String etime);
    //模型产品
    @Select("select count(t1.id) as num from t_farm_product t1\n" +
            "inner join t_farmland t2\n" +
            "on t1.farm=t2.id\n" +
            "where t2.code like concat(#{areaCode},'%') and t1.createtime between #{btime} and #{etime}")
    Integer getMoxinProject(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);
    //专家产品
    @Select("select sum(tt1.num) as num from (select count(1) as num from t_expertproduct t1 where  t1.areacode like concat(#{areaCode},'%') and t1.del_flg=0  and t1.createTime between #{btime} and #{etime}\n" +
            "union\n" +
            "select count(1) as num from t_expert_product t1 where t1.areacode like concat(#{areaCode},'%') and t1.createtime between #{btime} and #{etime})tt1\n")
    Integer getExpertProject(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);

    //农情
    @Select("select count(1) as num from t_agrinfo t1 where t1.del_flg=0 and t1.areacode like concat(#{areaCode},'%') and t1.createtime between #{btime} and #{etime} ")
    Integer getAgrNums(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);
    //灾情
    @Select("select count(1) as num from  t_disaster t1 where t1.del_flg=0 and t1.areacode like concat(#{areaCode},'%') and t1.createtime between #{btime} and #{etime} ")
    Integer getDisasterNums(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);

    //灾情
    @Select("select count(1) as num from t_advisoryinfo t1 where t1.del_flg=0 and t1.areacode like  concat(#{areaCode},'%') and  t1.uploadTime between #{btime} and #{etime} ")
    Integer getAdvNums(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);

    //农业主体
    @Select("select count(1) as num from t_user t1\n" +
            "inner join t_user_role t2\n" +
            "on t1.id=t2.userid \n" +
            "where  t1.del_flg=0 and t2.roleid in(1,2,4,6) and t1.areaCode  like  concat(#{areaCode},'%') and t1.createtime  between #{btime} and #{etime} ")
    Integer getZhutiNums(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);
    //专家人数
    @Select("select count(1) as num from t_user t1\n" +
            "inner join t_user_role t2\n" +
            "on t1.id=t2.userid \n" +
            "where  t1.del_flg=0 and t2.roleid in(3) and t1.areaCode  like  concat(#{areaCode},'%') and t1.createtime  between #{btime} and #{etime} ")
    Integer getExpertNums(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);
    //门户网站
    @Select("select count(1) as num from t_user t1\n" +
            "inner join t_user_role t2\n" +
            "on t1.id=t2.userid \n" +
            "where  t1.del_flg=0 and t2.roleid in(4,10) and t1.areaCode  like  concat(#{areaCode},'%') and t1.createtime  between #{btime} and #{etime} ")
    Integer getMenhuNums(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);
    //农田
    @Select("select count(1) as num from t_farmland t1 where t1.del_flg=0 and t1.code like  concat(#{areaCode},'%')   and t1.createtime   between #{btime} and #{etime}  ")
    Integer getFarmNums(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);



    //决策分组查询

    @Select("select areaname, areacode ,sum(num) num,group_concat(names order by num desc) names, group_concat(num order by num desc) nums from\n" +
            "(select ta.areaname, ta.areacode,sum(num) num,group_concat(distinct tb.group_name ) names from \n" +
            "(select b.areacode, b.areaname,\n" +
            "if(length(#{areaCode})=2 and #{areaCode} not in(11,12,31,50),concat(b.areacode,',',group_concat(distinct a.areacode)),group_concat(distinct a.areacode)) areacodes from\n" +
            "(select t1.areacode from cagmpisards.t_area t1 where t1.areacode like concat(#{areaCode},'%')) a\n" +
            "inner join \n" +
            "(select t1.areacode ,t1.areaname from cagmpisards.t_area t1 where \n" +
            "case \n" +
            "when (length(#{areaCode}) = 6 or (length(#{areaCode}) = 4 and left(#{areaCode},2) in(11,12,31,50)))then t1.areacode like concat(#{areaCode},'%') \n" +
            "else \n" +
            "t1.areacode like concat(#{areaCode},'%') and length(t1.areacode) = length(#{areaCode})+2\n" +
            "end) b\n" +
            "on  position(b.areacode in a.areacode)\n" +
            "group by b.areacode) ta\n" +
            "inner join\n" +
            "(select t1.id,t1.group_name,count(t2.id) num,t1.fit_areaid from  cqpamiss.t_b_productgroup t1\n" +
            "inner join cqpamiss.t_b_pms_product t2 on t1.id = t2.product_group\n" +
            "where t2.product_state = 6 and t2.create_date between #{btime} and #{etime}\n" +
            "group by t1.id) tb\n" +
            "on cqpamiss.f_in_array(ta.areacodes,tb.fit_areaid) =1\n" +
            "group by ta.areacode,tb.group_name\n" +
            "union all\n" +
            "select ta.areaname, ta.areacode,sum(num) num,group_concat(distinct tb.group_name ) names from \n" +
            "(select b.areacode, b.areaname,\n" +
            "if(length(#{areaCode})=2 and #{areaCode} not in(11,12,31,50),concat(b.areacode,',',group_concat(distinct a.areacode)),group_concat(distinct a.areacode)) areacodes from\n" +
            "(select t1.areacode  from cagmpisards.t_area t1 where t1.areacode like concat(#{areaCode},'%') ) a\n" +
            "inner join \n" +
            "(select t1.areacode,t1.areaname  from cagmpisards.t_area t1 where \n" +
            "case \n" +
            "when (length(#{areaCode}) = 6 or (length(#{areaCode}) = 4 and left(#{areaCode},2) in(11,12,31,50)))then t1.areacode like concat(#{areaCode},'%')\n" +
            "else \n" +
            "t1.areacode like concat(#{areaCode},'%') and length(t1.areacode) = length(#{areaCode})+2\n" +
            "end) b\n" +
            "on  position(b.areacode in a.areacode)\n" +
            "group by b.areacode) ta\n" +
            "inner join\n" +
            "(select t1.id,t1.group_name,count(t2.id) num,t1.fit_areaid from  china_pamiss.t_b_productgroup t1\n" +
            "inner join china_pamiss.t_b_pms_product t2 on t1.id = t2.product_group\n" +
            "where t2.product_state = 6 and t2.create_date between #{btime} and #{etime}\n" +
            "group by t1.id) tb\n" +
            "on cqpamiss.f_in_array(ta.areacodes,tb.fit_areaid) =1\n" +
            "group by ta.areacode,tb.group_name) tc\n" +
            "group by areacode \n" +
            "order by num desc\n")
    List<Map<String,Object>> getGroupJuece(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);
    //模型
    @Select("select areacode,areaname,sum(num) num,group_concat(modename order by num desc) names, group_concat(num order by num desc) nums from\n" +
            "(select t1.areacode, t1.areaname,t5.modename,count(t3.id) num from cagmpisards.t_area t1\n" +
            "inner join cagmpisards.t_farmland t2 on t1.areacode = t2.code\n" +
            "inner join cagmpisards.t_farm_product t3 on t2.id = t3.farm\n" +
            "inner join cagmpisards.t_area_product t4 on t3.qid =t4.id\n" +
            "inner join cagmpisards.t_mode_info t5 on t4.mid = t5.id\n" +
            "where convert(t3.createtime,CHAR) between (#{btime}) and  (#{etime})  and t1.areacode like concat(#{areaCode},'%')\n" +
            "group by  t1.areaid,t5.modename) t\n" +
            "group by areacode\n" +
            "order  by num desc;")
    List<Map<String,Object>> getGroupMoxin(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);
    //专家
    @Select("select areacode,areaname, sum(num) num, group_concat(name order by num desc) names, group_concat(num order by num desc) nums from\n" +
            "(select t1.areacode, t1.areaname,t4.name,count(t3.id) num from \n" +
            "(select b.areacode, a.areaname,\n" +
            "if(length(#{areaCode})=2 and #{areaCode} not in(11,12,31,50),concat(b.areacode,',',group_concat(distinct a.areacode)),group_concat(distinct a.areacode)) areacodes from\n" +
            "(select t1.areacode,t1.areaname  from cagmpisards.t_area t1 where t1.areacode like concat(#{areaCode},'%')) a\n" +
            "inner join \n" +
            "(select t1.areacode  from cagmpisards.t_area t1 where \n" +
            "case \n" +
            "when (length(#{areaCode}) = 6 or (length(#{areaCode}) = 4 and left(#{areaCode},2) in(11,12,31,50)))then t1.areacode like concat(#{areaCode},'%') \n" +
            "else \n" +
            "t1.areacode like concat(#{areaCode},'%') and length(t1.areacode) = length(#{areaCode})+2\n" +
            "end) b\n" +
            "on  position(b.areacode in a.areacode)\n" +
            "group by b.areacode) t1\n" +
            "inner join t_expertproduct t2 on  find_in_set(t2.areacode,t1.areacodes)\n" +
            "inner join t_expert_subject t3 on t2.expid = t3.expertId\n" +
            "inner join t_subject t4 on t3.subjectId = t4.id\n" +
            "where convert(t2.createtime,CHAR) between (#{btime}) and  (#{etime})  and t1.areacode like concat(#{areaCode},'%')\n" +
            "group by  t1.areacode,t4.id\n" +
            ") t\n" +
            "group by areacode\n" +
            "order  by num desc;")
    List<Map<String,Object>> getGroupZhuanjia(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);
    //互动咨询
    @Select("select areacode,areaname,(t.agrnum+advnum+disnum+farmnum) num,  concat('农情,','咨询,','灾害,','农事' )names,concat(agrnum,',',advnum,',',disnum,',',farmnum) nums from \n" +
            "(select tx1.areacode,tx1.areaname, if(tx2.num is null,0,tx2.num) agrnum ,if(tx3.num is null,0,tx3.num) advnum,if(tx4.num is null,0,tx4.num) disnum,if(tx5.num is null,0,tx5.num) farmnum from \n" +
            "(select b.areacode, a.areaname,\n" +
            "if(length(#{areaCode})=2 and #{areaCode} not in(11,12,31,50),concat(b.areacode,',',group_concat(distinct a.areacode)),group_concat(distinct a.areacode)) areacodes from\n" +
            "(select t1.areacode,t1.areaname  from cagmpisards.t_area t1 where t1.areacode like concat(#{areaCode},'%')) a\n" +
            "inner join \n" +
            "(select t1.areacode  from cagmpisards.t_area t1 where \n" +
            "case \n" +
            "when (length(#{areaCode}) = 6 or (length(#{areaCode}) = 4 and left(#{areaCode},2) in(11,12,31,50)))then t1.areacode like concat(#{areaCode},'%') \n" +
            "else \n" +
            "t1.areacode like concat(#{areaCode},'%') and length(t1.areacode) = length(#{areaCode})+2\n" +
            "end) b\n" +
            "on  position(b.areacode in a.areacode)\n" +
            "group by b.areacode) tx1\n" +
            "left join\n" +
            "(select areacode,areaname, sum(num) num, '农情' as name  from\n" +
            "(select t1.areacode, t1.areaname,count(distinct t2.id) num from \n" +
            "(select b.areacode, a.areaname,\n" +
            "if(length(#{areaCode})=2 and #{areaCode} not in(11,12,31,50),concat(b.areacode,',',group_concat(distinct a.areacode)),group_concat(distinct a.areacode)) areacodes from\n" +
            "(select t1.areacode,t1.areaname  from cagmpisards.t_area t1 where t1.areacode like concat(#{areaCode},'%')) a\n" +
            "inner join \n" +
            "(select t1.areacode  from cagmpisards.t_area t1 where \n" +
            "case \n" +
            "when (length(#{areaCode}) = 6 or (length(#{areaCode}) = 4 and left(#{areaCode},2) in(11,12,31,50)))then t1.areacode like concat(#{areaCode},'%') \n" +
            "else \n" +
            "t1.areacode like concat(#{areaCode},'%') and length(t1.areacode) = length(#{areaCode})+2\n" +
            "end) b\n" +
            "on  position(b.areacode in a.areacode)\n" +
            "group by b.areacode) t1\n" +
            "inner join t_agrinfo t2 on find_in_set(t2.areacode,t1.areacodes)\n" +
            "where convert(t2.createtime,CHAR) between (#{btime}) and  (#{etime})  and t1.areacode like concat(#{areaCode},'%')\n" +
            "group by  t1.areacode,t2.id\n" +
            ") t\n" +
            "group by areacode) tx2 on tx1.areacode = tx2.areacode\n" +
            "\n" +
            "left join \n" +
            "/* 咨询 */\n" +
            "(select areacode,areaname, sum(num) num, '咨询' as name  from\n" +
            "(select t1.areacode, t1.areaname,count(distinct t2.id) num from \n" +
            "(select b.areacode, a.areaname,\n" +
            "if(length(#{areaCode})=2 and #{areaCode} not in(11,12,31,50),concat(b.areacode,',',group_concat(distinct a.areacode)),group_concat(distinct a.areacode)) areacodes from\n" +
            "(select t1.areacode,t1.areaname  from cagmpisards.t_area t1 where t1.areacode like concat(#{areaCode},'%')) a\n" +
            "inner join \n" +
            "(select t1.areacode  from cagmpisards.t_area t1 where \n" +
            "case \n" +
            "when (length(#{areaCode}) = 6 or (length(#{areaCode}) = 4 and left(#{areaCode},2) in(11,12,31,50)))then t1.areacode like concat(#{areaCode},'%') \n" +
            "else \n" +
            "t1.areacode like concat(#{areaCode},'%') and length(t1.areacode) = length(#{areaCode})+2\n" +
            "end) b\n" +
            "on  position(b.areacode in a.areacode)\n" +
            "group by b.areacode) t1\n" +
            "inner join t_advisoryinfo t2 on find_in_set(t2.areacode,t1.areacodes)\n" +
            "where convert(t2.uploadTime,CHAR) between (#{btime}) and  (#{etime})  and t1.areacode like concat(#{areaCode},'%')\n" +
            "group by  t1.areacode,t2.id\n" +
            ") t\n" +
            "group by areacode) tx3 on tx1.areacode = tx3.areacode\n" +
            "left join\n" +
            "(select areacode,areaname, sum(num) num, '灾情' as name  from\n" +
            "(select t1.areacode, t1.areaname,count(distinct t2.id) num from \n" +
            "(select b.areacode, a.areaname,\n" +
            "if(length(#{areaCode})=2 and #{areaCode} not in(11,12,31,50),concat(b.areacode,',',group_concat(distinct a.areacode)),group_concat(distinct a.areacode)) areacodes from\n" +
            "(select t1.areacode,t1.areaname  from cagmpisards.t_area t1 where t1.areacode like concat(#{areaCode},'%')) a\n" +
            "inner join \n" +
            "(select t1.areacode  from cagmpisards.t_area t1 where \n" +
            "case \n" +
            "when (length(#{areaCode}) = 6 or (length(#{areaCode}) = 4 and left(#{areaCode},2) in(11,12,31,50)))then t1.areacode like concat(#{areaCode},'%') \n" +
            "else \n" +
            "t1.areacode like concat(#{areaCode},'%') and length(t1.areacode) = length(#{areaCode})+2\n" +
            "end) b\n" +
            "on  position(b.areacode in a.areacode)\n" +
            "group by b.areacode) t1\n" +
            "inner join t_disaster t2 on find_in_set(t2.areacode,t1.areacodes)\n" +
            "where convert(t2.createtime,CHAR) between (#{btime}) and  (#{etime})  and t1.areacode like concat(#{areaCode},'%')\n" +
            "group by  t1.areacode,t2.id\n" +
            ") t\n" +
            "group by areacode) tx4 on tx1.areacode = tx4.areacode\n" +
            "\n" +
            "left join\n" +
            "(select areacode,areaname, sum(num) num, '农事' as name  from\n" +
            "(select t1.areacode, t1.areaname,count(distinct t2.id) num from \n" +
            "(select b.areacode, a.areaname,\n" +
            "if(length(#{areaCode})=2 and #{areaCode} not in(11,12,31,50),concat(b.areacode,',',group_concat(distinct a.areacode)),group_concat(distinct a.areacode)) areacodes from\n" +
            "(select t1.areacode,t1.areaname  from cagmpisards.t_area t1 where t1.areacode like concat(#{areaCode},'%')) a\n" +
            "inner join \n" +
            "(select t1.areacode  from cagmpisards.t_area t1 where \n" +
            "case \n" +
            "when (length(#{areaCode}) = 6 or (length(#{areaCode}) = 4 and left(#{areaCode},2) in(11,12,31,50)))then t1.areacode like concat(#{areaCode},'%') \n" +
            "else \n" +
            "t1.areacode like concat(#{areaCode},'%') and length(t1.areacode) = length(#{areaCode})+2\n" +
            "end) b\n" +
            "on  position(b.areacode in a.areacode)\n" +
            "group by b.areacode) t1\n" +
            "inner join t_farming t2 on find_in_set(t2.areacode,t1.areacodes)\n" +
            "where convert(t2.recordtime,CHAR) between (#{btime}) and  (#{etime})  and t1.areacode like concat(#{areaCode},'%')\n" +
            "group by  t1.areacode,t2.id\n" +
            ") t\n" +
            "group by areacode) tx5  on tx1.areacode = tx5.areacode\n" +
            "order by agrnum desc,advnum desc,disnum desc,farmnum desc)t\n" +
            "where t.agrnum+advnum+disnum+farmnum > 0  order by num desc;")
    List<Map<String,Object>> getGroupHudongzixun(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);

    //主体
    @Select("select areacode,areaname, sum(num) num, group_concat(name order by num desc) names, group_concat(num order by num desc) nums from\n" +
            "(select t1.areacode, t1.areaname,t4.name,count(t3.id) num from \n" +
            "(select b.areacode, a.areaname,\n" +
            "if(length(#{areaCode})=2 and #{areaCode} not in(11,12,31,50),concat(b.areacode,',',group_concat(distinct a.areacode)),group_concat(distinct a.areacode)) areacodes from\n" +
            "(select t1.areacode,t1.areaname  from cagmpisards.t_area t1 where t1.areacode like concat(#{areaCode},'%')) a\n" +
            "inner join \n" +
            "(select t1.areacode  from cagmpisards.t_area t1 where \n" +
            "case \n" +
            "when (length(#{areaCode}) = 6 or (length(#{areaCode}) = 4 and left(#{areaCode},2) in(11,12,31,50)))then t1.areacode like concat(#{areaCode},'%') \n" +
            "else \n" +
            "t1.areacode like concat(#{areaCode},'%') and length(t1.areacode) = length(#{areaCode})+2\n" +
            "end) b\n" +
            "on  position(b.areacode in a.areacode)\n" +
            "group by b.areacode) t1\n" +
            "inner join t_user t2 on  find_in_set(t2.areacode,t1.areacodes)\n" +
            "inner join t_user_role t3 on t2.id = t3.userid\n" +
            "inner join t_role t4 on t3.roleid = t4.id\n" +
            "where t4.id in (1,2,4,6) and t2.del_flg =0  and convert(t2.createtime,CHAR) between (#{btime}) and  (#{etime})  and t1.areacode like concat(#{areaCode},'%')\n" +
            "group by  t1.areacode,t4.id\n" +
            ") t\n" +
            "group by areacode\n" +
            "order  by num desc;")
    List<Map<String,Object>> getGroupZhuti(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);

    //专家
    @Select("\n" +
            "/* 专家 */\n" +
            "select areacode,areaname, sum(num) num, group_concat(name order by num desc) names, group_concat(num order by num desc) nums from\n" +
            "(select t1.areacode, t1.areaname,t4.name,count(t3.id) num from \n" +
            "(select b.areacode, a.areaname,\n" +
            "if(length(#{areaCode})=2 and #{areaCode} not in(11,12,31,50),concat(b.areacode,',',group_concat(distinct a.areacode)),group_concat(distinct a.areacode)) areacodes from\n" +
            "(select t1.areacode,t1.areaname  from cagmpisards.t_area t1 where t1.areacode like concat(#{areaCode},'%')) a\n" +
            "inner join \n" +
            "(select t1.areacode  from cagmpisards.t_area t1 where \n" +
            "case \n" +
            "when (length(#{areaCode}) = 6 or (length(#{areaCode}) = 4 and left(#{areaCode},2) in(11,12,31,50)))then t1.areacode like concat(#{areaCode},'%') \n" +
            "else \n" +
            "t1.areacode like concat(#{areaCode},'%') and length(t1.areacode) = length(#{areaCode})+2\n" +
            "end) b\n" +
            "on  position(b.areacode in a.areacode)\n" +
            "group by b.areacode) t1\n" +
            "inner join t_user t2 on  find_in_set(t2.areacode,t1.areacodes)\n" +
            "inner join t_expertinfo t0 on  t2.id = t0.userid\n" +
            "inner join t_expert_subject t3 on t0.id = t3.expertId\n" +
            "inner join t_subject t4 on t3.subjectId = t4.id\n" +
            "where t2.del_flg =0 and t0.del_flg = 0 and convert(t2.createtime,CHAR) between (#{btime}) and  (#{etime})  and t1.areacode like concat(#{areaCode},'%')\n" +
            "group by  t1.areacode,t4.id\n" +
            ") t\n" +
            "group by areacode\n" +
            "order  by num desc;")
    List<Map<String,Object>> getGroupZj(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);

    //门户
    @Select("\n" +
            "/* 门户网站 */\n" +
            "select areacode,areaname, sum(num) num, group_concat(name order by num desc) names, group_concat(num order by num desc) nums from\n" +
            "(select t1.areacode, t1.areaname,t4.name,count(t3.id) num from \n" +
            "(select b.areacode, a.areaname,\n" +
            "if(length(#{areaCode})=2 and #{areaCode} not in(11,12,31,50),concat(b.areacode,',',group_concat(distinct a.areacode)),group_concat(distinct a.areacode)) areacodes from\n" +
            "(select t1.areacode,t1.areaname  from cagmpisards.t_area t1 where t1.areacode like concat(#{areaCode},'%')) a\n" +
            "inner join \n" +
            "(select t1.areacode  from cagmpisards.t_area t1 where \n" +
            "case \n" +
            "when (length(#{areaCode}) = 6 or (length(#{areaCode}) = 4 and left(#{areaCode},2) in(11,12,31,50)))then t1.areacode like concat(#{areaCode},'%') \n" +
            "else \n" +
            "t1.areacode like concat(#{areaCode},'%') and length(t1.areacode) = length(#{areaCode})+2\n" +
            "end) b\n" +
            "on  position(b.areacode in a.areacode)\n" +
            "group by b.areacode) t1\n" +
            "inner join t_user t2 on  find_in_set(t2.areacode,t1.areacodes)\n" +
            "inner join cq_market.user  t3 on  t2.loginName =  CONVERT(t3.username USING utf8) COLLATE utf8_unicode_ci \n" +
            "inner join t_role t4 on   t4.code =  CONVERT(t3.role_code USING utf8) COLLATE utf8_unicode_ci \n" +
            "where  t2.areaCode like concat(#{areaCode},'%') and  t2.del_flg =0  and convert(t2.createtime,CHAR) between (#{btime}) and  (#{etime})  and t1.areacode like concat(#{areaCode},'%')\n" +
            "group by  t1.areacode,t4.id\n" +
            ") t\n" +
            "group by areacode\n" +
            "order  by num desc;\n")
    List<Map<String,Object>> getGroupMenhu(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);

    //农田
    @Select("/* 农田 */\n" +
            "select areacode,areaname, sum(num) num, group_concat(name order by num desc) names, group_concat(num order by num desc) nums from\n" +
            "(select t1.areacode, t1.areaname,t5.name,count(t3.id) num from \n" +
            "(select b.areacode, a.areaname,\n" +
            "if(length(#{areaCode})=2 and #{areaCode} not in(11,12,31,50),concat(b.areacode,',',group_concat(distinct a.areacode)),group_concat(distinct a.areacode)) areacodes from\n" +
            "(select t1.areacode,t1.areaname  from cagmpisards.t_area t1 where t1.areacode like concat(#{areaCode},'%')) a\n" +
            "inner join \n" +
            "(select t1.areacode  from cagmpisards.t_area t1 where \n" +
            "case \n" +
            "when (length(#{areaCode}) = 6 or (length(#{areaCode}) = 4 and left(#{areaCode},2) in(11,12,31,50)))then t1.areacode like concat(#{areaCode},'%') \n" +
            "else \n" +
            "t1.areacode like concat(#{areaCode},'%') and length(t1.areacode) = length(#{areaCode})+2\n" +
            "end) b\n" +
            "on  position(b.areacode in a.areacode)\n" +
            "group by b.areacode) t1\n" +
            "inner join t_farmland t2 on  find_in_set(t2.code,t1.areacodes)\n" +
            "inner join t_farmcrops t3 on t2.id = t3.farmland\n" +
            "inner join t_crop t4 on t3.crop = t4.code\n" +
            "inner join t_crop t5 on t4.parentid = t5.id\n" +
            "where convert(t2.createtime,CHAR) between (#{btime}) and  (#{etime})  and t1.areacode like concat(#{areaCode},'%')\n" +
            "group by  t1.areacode,t5.id\n" +
            ") t\n" +
            "group by areacode\n" +
            "order  by num desc; ")
    List<Map<String,Object>> getGroupFarm(@Param("areaCode")String areaCode,@Param("btime")String btime,@Param("etime")String etime);



}
